from db.mysql_db import pool


class NewsDao:
    # 查询未审批新闻列表
    def search_unreview_list(self, page):
        try:
            con = pool.get_connection()
            cursor = con.cursor()
            sql = f"""
                  SELECT
                    n.id,
                    n.title,
                    n.state,
                    t.type,
                    u.username
                  FROM
                    t_news n
                    JOIN t_type t ON n.type_id = t.id
                    JOIN t_user u ON n.editor_id = u.id
                  WHERE
                    state = %s
                  ORDER BY
                    create_time DESC
                    LIMIT %s,
                    %s
                  """
            cursor.execute(sql, ("未审核", (page - 1) * 10, 10))
            result = cursor.fetchall()
            return result
        finally:
            if "con" in dir():
                con.close()

    # 查询待审批新闻的总页数
    def search_unreview_count_page(self):
        try:
            con = pool.get_connection()
            cursor = con.cursor()
            sql = "SELECT CEIL(COUNT(*)/10) FROM t_news WHERE state=%s"
            cursor.execute(sql, ["未审核"])
            result = cursor.fetchone()[0]
            return result
        except Exception as e:
            print(e)
        finally:
            if "con" in dir():
                con.close()

    # 查询新闻总页数
    def search_count_page(self):
        try:
            con = pool.get_connection()
            cursor = con.cursor()
            sql = "SELECT CEIL(COUNT(*)/10) FROM t_news"
            cursor.execute(sql)
            result = cursor.fetchone()[0]
            return result
        except Exception as e:
            print(e)
        finally:
            if "con" in dir():
                con.close()

    # 查询新闻列表
    def search_list(self, page):
        try:
            con = pool.get_connection()
            cursor = con.cursor()
            sql = """
                SELECT
                  n.id,
                  n.title,
                  n.state,
                  t.type,
                  u.username
                FROM
                  t_news n
                  JOIN t_user u ON n.editor_id = u.id
                  JOIN t_type t ON n.type_id = t.id
                  LIMIT %s,
                  %s
              """
            cursor.execute(sql, ((page - 1) * 10, 10))
            result = cursor.fetchall()
            return result
        except Exception as e:
            print(e)
        finally:
            if "con" in dir():
                con.close()

    # 删除新闻
    def delete_by_id(self, id):
        try:
            con = pool.get_connection()
            con.start_transaction()
            cursor = con.cursor()
            sql = """
                    DELETE
                    FROM
                      t_news
                    WHERE
                      id = %s
                  """
            cursor.execute(sql, [id])
            con.commit()
            result = cursor.fetchone()[0]
            return result
        except Exception as e:
            if "con" in dir():
                con.rollback()
        finally:
            if "con" in dir():
                con.close()

    # 审批新闻
    def update_unreview_news(self, id):
        try:
            con = pool.get_connection()
            con.start_transaction()
            cursor = con.cursor()
            sql = """
                    UPDATE
                      t_news
                    SET
                      state = %s
                    WHERE
                      id = %s
                  """
            cursor.execute(sql, ("已审核", id))
            con.commit()
        except Exception as e:
            if "con" in dir():
                con.rollback()
        finally:
            if "con" in dir():
                con.close()
